package load;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import tools.Tools;

public class DataBaseLoader {

    public static Connection civi6;

    public static void createDatabases() throws SQLException {
        File file = new File("civi6.db");
        if (file.exists())
            file.delete();
        civi6 = DriverManager.getConnection("jdbc:sqlite:civi6.db");
    }

    public static int numberOf (String s, char c) {
        int n = 0;
        for (int i = 0; i < s.length(); i++) {
            if (s.charAt(i) == c) {
                n ++;
            }
        }
        return n;
    }

    public static void loadSQL(File file, Statement statement) throws Exception {
        String text = Tools.readFromFile(file);
        String[] lines = (text + " ").split("(;\\s*\\n|;\\s*(?=--))");
        //String[] lines = (text + " ").split(";");
        for (int i = 0; i < lines.length - 1; i++) {
            if (lines[i] == null) {
                continue;
            }
            //if (numberOf(lines[i], '"') % 2 == 1) {
            //    lines[i + 1] = lines[i] + ";" + lines[i + 1];
            //    continue;
            //}
            if (lines[i].contains("CREATE TRIGGER") && lines[i].contains("BEGIN") && !lines[i].contains("END")) {
                lines[i + 1] = lines[i] + ";" + lines[i + 1];
                continue;
            }
            if (lines[i].contains("Make_Hash")) {
                continue;
            }
            lines[i] = lines[i].replaceAll("'Index'", "'Idx'");
            try {
                statement.execute(lines[i]);
            } catch (Exception e) {
                if (!e.getMessage().contains("The prepared statement has been finalized")) {
                    System.out.println();
                    e.printStackTrace();
                    System.err.println(e.getClass().getName() + " " + e.getMessage());
                    if (Init.loading != null) {
                        System.err.println(Init.loading.getAbsolutePath());
                        Init.errorFiles.add(Init.loading);
                    }
                }
            }
        }
    }

    public static void loadXMLAsTables(File file, Statement statement) throws ParserConfigurationException, IOException, SAXException {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.parse(file);
        Element root = doc.getDocumentElement();
        NodeList list = root.getElementsByTagName("Table");
        for (int i = 0; i < list.getLength(); i++) {
            Element table = (Element) list.item(i);
            String name = table.getAttribute("name");
            NodeList columns = table.getElementsByTagName("Column");
            String s = "";
            for (int j = 0; j < columns.getLength(); j++) {
                Element column = (Element) columns.item(j);
                String term = "\"" + column.getAttribute("name") + "\" " + column.getAttribute("type");
                if ("true".equals(column.getAttribute("notnull"))) {
                    term += " not null";
                }
                if ("unique".equals(column.getAttribute("notnull"))) {
                    term += " unique";
                }
                if ("primarykey".equals(column.getAttribute("notnull"))) {
                    term += " primary key";
                }
                if (column.getAttribute("default") != null) {
                    term += " default \"" + column.getAttribute("default") + "\"";
                }
                s += term + ", ";
            }
            s = s.substring(0, s.length() - 2);
            String command = "create table \"" + name + "\" (" + s + ");";
            command = command.replaceAll("'Index'", "'Idx'");
            try {
                statement.execute(command);
            } catch (Exception e) {
                System.err.println(e.getClass().getName() + " " + e.getMessage());
                if (Init.loading != null) {
                    System.err.println(Init.loading.getAbsolutePath());
                    Init.errorFiles.add(Init.loading);
                }
            }
        }
    }

    public static void excuteRow(Node node, Statement statement, String tableName) throws Exception {
        Element row = (Element) node;
        String keys = "";
        String values = "";

        NamedNodeMap map = row.getAttributes();
        for (int k = 0; k < map.getLength(); k++) {
            String key = map.item(k).getNodeName();
            if (key.equals("Index")) {
                key = "Idx";
            }
            keys += key;
            String context = map.item(k).getTextContent();
            if (context.toLowerCase().equals("true") || context.toLowerCase().equals("false")) {
                values += context;
            } else {
                values += "'" + context + "'";
            }
            keys += ", ";
            values += ", ";
        }

        NodeList list = row.getChildNodes();
        for (int k = 0; k < list.getLength(); k++) {
            if (list.item(k) instanceof Element) {
                String key = list.item(k).getNodeName();
                if (key.equals("Index")) {
                    key = "Idx";
                }
                keys += key;
                String context = list.item(k).getTextContent().replaceAll("'", "''");
                if (context.toLowerCase().equals("true") || context.toLowerCase().equals("false")) {
                    values += context;
                } else {
                    values += "'" + context + "'";
                }
                keys += ", ";
                values += ", ";
            }
        }

        keys = keys.substring(0, keys.length() - 2);
        values = values.substring(0, values.length() - 2);

        String command = "insert or replace into " + tableName + " (" + keys + ") values (" + values + ");";

        try {
            statement.execute(command);
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + " " + e.getMessage());
            if (Init.loading != null) {
                System.err.println(Init.loading.getAbsolutePath());
                Init.errorFiles.add(Init.loading);
            }
        }

    }

    public static void loadXMLAsData(File file, Statement statement) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.parse(file);
        Element root = doc.getDocumentElement();
        NodeList tables = root.getChildNodes();
        for (int i = 0; i < tables.getLength(); i++) {
            Node node = tables.item(i);
            if (node instanceof Element) {
                Element table = (Element) node;
                String tableName = table.getTagName();
                NodeList rows = table.getElementsByTagName("Row");
                for (int j = 0; j < rows.getLength(); j++) {
                    excuteRow(rows.item(j), statement, tableName);
                }
                NodeList replaces = table.getElementsByTagName("Replace");
                for (int j = 0; j < replaces.getLength(); j++) {
                    excuteRow(replaces.item(j), statement, tableName);
                }
            }
        }
    }

    public static void loadAsData(File file, Statement statement) throws Exception {
        if (file.getName().endsWith(".xml")) {
            loadXMLAsData(file, statement);
        } else if (file.getName().endsWith(".sql")) {
            loadSQL(file, statement);
        }
    }

    public static void loadAllXMLAsData(File file, Statement statement) throws Exception {
        if (file.isFile() && file.getName().endsWith(".xml")) {
            loadXMLAsData(file, statement);
        } else if (file.isDirectory()) {
            for (File child : file.listFiles()) {
                loadAllXMLAsData(child, statement);
            }
        }
    }
}
